package database;

import javax.swing.*;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;

import java.text.NumberFormat;

import javax.swing.JButton;
import javax.swing.JComboBox;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JTextField;

import java.util.ArrayList;

public class AirlineGui {

        String usr;
        char[] pass;

        private JFrame frame, passengerFrame, aircraftFrame;
        private JPanel panel, passengerPanel, aircraftPanel;
        private JButton passenger, aircraft, exit;
        private JButton reserve, checkin, delRes, back1;
        private JButton schedule, update, postReport, delFlight, back2;

        private String[] cities = { "New York", "Washington D.C.", "Baltimore",
                        "L.A.", "San Francisco", "Seattle", "Chicago", "Newark", "Detroit",
                        "Boston", "Raleigh", "Tucson", "Columbus", "Tampa", "Houston",
                        "Las Vegas", "Minneapolis", "Atlanta", "Pittsburgh", "Toronto" };

        private String[] months = { "Jan", "Feb", "Mar", "April", "May", "June",
                        "July", "Aug", "Sept", "Oct", "Nov", "Dec" };

        private String[] aircraftType = { "Boeing 737", "Boeing 747", "Boeing 767",
                        "Airbus 330", "Airbus 340", "Airbus 380" };

        private int[] occupancy = { 126, 467, 181, 335, 375, 300, 525 };
        private String[] days = new String[31];
        private Integer[] years = new Integer[6];

        private NumberFormat noCommaFormat;

        private static Database base;

        /**
         * @param args
         *            Starts the gui
         */
        public static void main(String[] args) {
                AirlineGui gui = new AirlineGui();
                base = new Database();
                // base.connectJDBC("br85570", "asdf");
        }

        /**
         * AirlineGui constructor
         */
        public AirlineGui() {

                noCommaFormat = NumberFormat.getIntegerInstance();
                noCommaFormat.setGroupingUsed(false);
                // gets a list of numbers properly formatted for the days of the
                // calendar
                for (int i = 0; i <= 30; i++) {
                        if (i < 9)
                                days[i] = "0" + (i + 1);
                        else
                                days[i] = "" + (i + 1);
                }
                
                // gets the year for the next five years starting from 2013
                for (int i = 0; i <= 5; i++) {
                        years[i] = new Integer(i + 2013);
                }

                final JFrame loginFrame = new JFrame("Login");
                loginFrame.requestFocusInWindow();
                loginFrame.setLocation(50, 50);
                loginFrame.setSize(500, 500);
                loginFrame.setResizable(false);
                loginFrame.setAlwaysOnTop(true);
                loginFrame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
                loginFrame.setVisible(true);

                JLabel userText = new JLabel("user name:");
                final JTextField user = new JTextField(10);
                JPanel userPanel = new JPanel();
                userPanel.add(userText);
                userPanel.add(user);
                JLabel passwordText = new JLabel("password:");
                final JPasswordField password = new JPasswordField(10);
                JPanel passPanel = new JPanel();
                passPanel.add(passwordText);
                passPanel.add(password);

                JButton submit = new JButton("Submit");
                JButton exit = new JButton("Exit");
                JPanel buttonPanel = new JPanel();
                buttonPanel.add(submit);
                buttonPanel.add(exit);

                submit.addActionListener(new ActionListener() {
                        public void actionPerformed(ActionEvent e) {
                                usr = user.getText();
                                pass = password.getPassword();
                                try {
                                        base.connectJDBC(usr, new String(pass));
                                } catch (Exception userWrong) {
                                        JOptionPane.showMessageDialog(loginFrame,
                                                        "Invalid Username and Password.");
                                        System.exit(1);
                                }
                                loginFrame.dispose();
                                login();
                        }
                });

                exit.addActionListener(new ActionListener() {
                        public void actionPerformed(ActionEvent e) {
                                System.exit(-1);
                        }
                });

                JPanel logoPanel = new JPanel();
                ImageIcon logo = new ImageIcon("logo.jpg");
                logoPanel.add(new JLabel(logo));

                JPanel loginPanel = new JPanel();
                loginPanel.setLayout(new BoxLayout(loginPanel, 3));
                loginPanel.add(userPanel);
                loginPanel.add(passPanel);
                loginPanel.add(buttonPanel);
                loginPanel.add(logoPanel);

                loginFrame.getContentPane().add(loginPanel);
                loginPanel.revalidate();
        }

        public void login() {
                frame = new JFrame("Airline Database");
                frame.requestFocusInWindow();
                frame.setLocation(50, 50);
                frame.setSize(500, 500);
                frame.setResizable(false);
                frame.setAlwaysOnTop(true);
                frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
                frame.setVisible(true);

                panel = new JPanel();
                passenger = new JButton("Passenger Information");
                aircraft = new JButton("Aircraft Information");
                exit = new JButton("Exit");
                panel.add(passenger);
                panel.add(aircraft);
                panel.add(exit);
                frame.getContentPane().add(panel);

                passengerFrame = new JFrame("Update Passenger Information");
                passengerFrame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
                passengerFrame.setSize(500, 500);
                passengerFrame.setResizable(false);
                passengerFrame.setAlwaysOnTop(true);
                passengerFrame.setVisible(false);
                passengerPanel = new JPanel();
                reserve = new JButton("Reserve Flight");
                checkin = new JButton("Checkin Passenger");
                delRes = new JButton("Delete Reservation");
                back1 = new JButton("Previous Menu");
                passengerPanel.add(reserve);
                passengerPanel.add(checkin);
                passengerPanel.add(delRes);
                passengerPanel.add(back1);
                passengerFrame.getContentPane().add(passengerPanel);

                aircraftFrame = new JFrame("Update Aircraft Infromation");
                aircraftFrame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
                aircraftFrame.setSize(500, 500);
                aircraftFrame.setResizable(false);
                aircraftFrame.setAlwaysOnTop(true);
                aircraftFrame.setVisible(false);
                aircraftPanel = new JPanel();
                schedule = new JButton("Schedule New Flight");
                update = new JButton("Update Flight");
                postReport = new JButton("Print Post Flight Report");
                delFlight = new JButton("Cancel Flight");
                back2 = new JButton("Previous Menu");
                aircraftPanel.add(schedule);
                aircraftPanel.add(update);
                aircraftPanel.add(postReport);
                aircraftPanel.add(delFlight);
                aircraftPanel.add(back2);
                aircraftFrame.getContentPane().add(aircraftPanel);

                passenger.addActionListener(new ActionListener() {
                        public void actionPerformed(ActionEvent e) {
                                passengerFrame.setLocation(frame.getLocation());
                                passengerFrame.setVisible(true);
                                frame.setVisible(false);
                        }
                });

                aircraft.addActionListener(new ActionListener() {
                        public void actionPerformed(ActionEvent e) {
                                aircraftFrame.setLocation(frame.getLocation());
                                aircraftFrame.setVisible(true);
                                frame.setVisible(false);
                        }
                });

                back1.addActionListener(new ActionListener() {
                        public void actionPerformed(ActionEvent e) {
                                frame.setLocation(passengerFrame.getLocation());
                                passengerFrame.setVisible(false);
                                frame.setVisible(true);
                        }
                });

                back2.addActionListener(new ActionListener() {
                        public void actionPerformed(ActionEvent e) {
                                frame.setLocation(aircraftFrame.getLocation());
                                aircraftFrame.setVisible(false);
                                frame.setVisible(true);
                        }
                });

                exit.addActionListener(new ActionListener() {
                        public void actionPerformed(ActionEvent e) {
                                System.exit(0);
                        }
                });

                reserve.addActionListener(new ActionListener() {
                        public void actionPerformed(ActionEvent e) {
                                reserve();
                        }
                });

                checkin.addActionListener(new ActionListener() {
                        public void actionPerformed(ActionEvent e) {
                                checkin();
                        }
                });

                schedule.addActionListener(new ActionListener() {
                        public void actionPerformed(ActionEvent e) {
                                schedule();
                        }
                });

                delRes.addActionListener(new ActionListener() {
                        public void actionPerformed(ActionEvent e) {
                                deleteReservation();
                        }
                });

                update.addActionListener(new ActionListener() {
                        public void actionPerformed(ActionEvent e) {
                                update();
                        }
                });

                postReport.addActionListener(new ActionListener() {
                        public void actionPerformed(ActionEvent e) {
                                postReport();
                        }
                });

                delFlight.addActionListener(new ActionListener() {
                        public void actionPerformed(ActionEvent e) {
                                deleteFlight();
                        }
                });

        }

        /**
         * Opens the reservation page
         */
        private void reserve() {

                final JFrame reserveFrame = new JFrame("Passenger Reservation");
                reserveFrame.setDefaultCloseOperation(JFrame.DO_NOTHING_ON_CLOSE);
                reserveFrame.setSize(1000, 600);
                reserveFrame.setResizable(false);
                reserveFrame.setAlwaysOnTop(true);
                reserveFrame.setVisible(true);
                reserveFrame.setLocation(passengerFrame.getLocation());
                passengerFrame.setVisible(false);

                NumberFormat ageFormat = NumberFormat.getIntegerInstance();
                ageFormat.setMaximumIntegerDigits(2);
                ageFormat.setGroupingUsed(false);
                NumberFormat amountFormat = NumberFormat.getNumberInstance();
                amountFormat.setMaximumFractionDigits(2);
                amountFormat.setGroupingUsed(false);
                NumberFormat ssnFormat = NumberFormat.getIntegerInstance();
                ssnFormat.setMaximumIntegerDigits(9);
                ssnFormat.setMinimumIntegerDigits(9);
                ssnFormat.setGroupingUsed(false);
                NumberFormat zipFormat = NumberFormat.getIntegerInstance();
                zipFormat.setMaximumIntegerDigits(5);
                zipFormat.setMinimumIntegerDigits(5);
                zipFormat.setGroupingUsed(false);
                NumberFormat phoneFormat = NumberFormat.getIntegerInstance();
                phoneFormat.setMaximumIntegerDigits(10);
                phoneFormat.setMinimumIntegerDigits(10);
                phoneFormat.setGroupingUsed(false);

                JPanel reservePanel = new JPanel();
                reservePanel.setLayout(new GridLayout(0, 2));
                JLabel ssnText = new JLabel("Social Security Number *: ");
                final JFormattedTextField ssn = new JFormattedTextField(ssnFormat);
                JLabel fnameText = new JLabel("First Name *: ");
                final JTextField fname = new JTextField();
                JLabel lnameText = new JLabel("Last Name *: ");
                final JTextField lname = new JTextField();
                JLabel ageText = new JLabel("Age: ");
                final JFormattedTextField age = new JFormattedTextField(ageFormat);
                JLabel addressText = new JLabel("Street *: ");
                final JTextField address = new JTextField();
                JLabel aptText = new JLabel("Apartment Number:");
                final JTextField apt = new JTextField();
                JLabel cityText = new JLabel("City *: ");
                final JTextField city = new JTextField();
                JLabel stateText = new JLabel("State *: ");
                final JTextField state = new JTextField();
                JLabel zipText = new JLabel("Zip *: ");
                final JFormattedTextField zip = new JFormattedTextField(zipFormat);
                JLabel teleHomeText = new JLabel("Home Telephone Number *: ");
                final JFormattedTextField home = new JFormattedTextField(phoneFormat);
                JLabel teleOfficeText = new JLabel("Office Telephone Number: ");
                final JFormattedTextField office = new JFormattedTextField(phoneFormat);
                JLabel emailText = new JLabel("Email Address: ");
                final JTextField email = new JTextField();
                JLabel classText = new JLabel("Flight Class: ");
                String[] classType = { "First", "Business", "Economy" };
                final JComboBox flightclass = new JComboBox(classType);
                JLabel paidText = new JLabel("Amount Paid *: ");
                final JFormattedTextField paid = new JFormattedTextField(amountFormat);
                JLabel flightText = new JLabel("Flight Number *: ");
                final JComboBox flight = new JComboBox();
                JLabel srcText = new JLabel("Source City: ");
                final JComboBox source = new JComboBox(cities);
                source.setSelectedItem(null);
                JLabel dstText = new JLabel("Destination City: ");
                final JComboBox dst = new JComboBox(cities);
                dst.setSelectedItem(null);
                JLabel dateText = new JLabel("Date: ");
                final JComboBox monthDate = new JComboBox(months);
                final JComboBox dayDate = new JComboBox(days);
                final JComboBox yearDate = new JComboBox(years);
                JPanel date = new JPanel();
                date.add(monthDate);
                date.add(dayDate);
                date.add(yearDate);

                final FlightSelectorWrapper flightWrapper = new FlightSelectorWrapper();

                JButton findFlight = new JButton("Find Flight");
                findFlight.addActionListener(new ActionListener() {
                        public void actionPerformed(ActionEvent e) {
                                
                                // Make sure the date is valid
                                String m = (String) monthDate.getSelectedItem();
                                int d = Integer.parseInt((String) dayDate.getSelectedItem());
                                int y = (Integer) yearDate.getSelectedItem();
                                if (!AirlineGui.validateDate(m, d, y)) {
                                        JOptionPane.showMessageDialog(reserveFrame,
                                                        "Date is invalid");
                                        return;
                                }

                                // make sure important things can't be left blank
                                if (source.getSelectedItem() == null
                                                || dst.getSelectedItem() == null) {
                                        JOptionPane.showMessageDialog(reserveFrame,
                                                        "One or more required fields were left blank.");
                                        return;
                                }

                                // populate flight numbers
                                String month;
                                if (monthDate.getSelectedIndex() + 1 < 10)
                                        month = "0" + (monthDate.getSelectedIndex() + 1);
                                else
                                        month = "" + (monthDate.getSelectedIndex() + 1);

                                String sql = "select f1.flight_num, f1.source_rank, f2.dest_rank, to_char(f2.arrive_ts, 'HH24:MI'), to_char(f1.depart_ts, 'HH24:MI') from FLIGHT f1, FLIGHT f2  "
                                                + "where to_char(f1.depart_TS, 'MMDDYYYY')='"
                                                + month
                                                + dayDate.getSelectedItem()
                                                + yearDate.getSelectedItem()
                                                + "' and to_char(f2.depart_TS, 'MMDDYYYY') ='"
                                                + month
                                                + dayDate.getSelectedItem()
                                                + yearDate.getSelectedItem()
                                                + "' and f2.destination='"
                                                + dst.getSelectedItem()
                                                + "' and f1.source='"
                                                + source.getSelectedItem()
                                                + "' and "
                                                + "f1.flight_num=f2.flight_num and "
                                                + "f1.source_rank < f2.dest_rank";
                                                String[] flightInfo = base.executeQuery(sql, 5);

                                if (flightInfo == null) {
                                        flightWrapper.setEmpty();
                                } else {

                                        flightWrapper.populate(flightInfo);

                                }
                        }
                });

                JButton submit = new JButton("Submit");
                submit.addActionListener(new ActionListener() {
                        public void actionPerformed(ActionEvent e) {
                                
                                // make sure date is valid
                                String m = (String) monthDate.getSelectedItem();
                                int d = Integer.parseInt((String) dayDate.getSelectedItem());
                                int y = (Integer) yearDate.getSelectedItem();
                                if (!AirlineGui.validateDate(m, d, y)) {
                                        JOptionPane.showMessageDialog(reserveFrame,
                                                        "Date is invalid");
                                        return;
                                }
                                // make sure important things can't be left blank
                                if (ssn.getText().equals("") || fname.getText().equals("")
                                                || lname.getText().equals("")
                                                || address.getText().equals("")
                                                || city.getText().equals("")
                                                || state.getText().equals("")
                                                || zip.getText().equals("")
                                                || home.getText().equals("")
                                                || paid.getText().equals("")) {
                                        JOptionPane.showMessageDialog(reserveFrame,
                                                        "One or more required fields were left blank.");
                                        return;
                                }
                                
                                if (source.getSelectedItem() == null
                                                || dst.getSelectedItem() == null) {
                                        JOptionPane.showMessageDialog(reserveFrame,
                                                        "Both source and destination must be selected");
                                        return;
                                }

                                String selectedFlight = flightWrapper.getSelectedFlight();

                                if (selectedFlight == null) {
                                        JOptionPane.showMessageDialog(reserveFrame,
                                                        "No flight was selected");
                                        return;
                                }
                                // flight num, source_rank, dest_rank, arrive time, depart time
                                String[] flight = selectedFlight.split(",");
                                String month;
                                // make month the correct format
                                if (monthDate.getSelectedIndex() < 10)
                                        month = "0" + (monthDate.getSelectedIndex() + 1);
                                else
                                        month = "" + (monthDate.getSelectedIndex() + 1);
                                
                                // check that the reservation does not exist already
                                String reserveExist = "select ssn from reservation where flight_num = "
                                                + flight[0]
                                                + " and to_char(depart_ts, 'MMDDYYYY') =  '"
                                                + month
                                                + dayDate.getSelectedItem()
                                                + yearDate.getSelectedItem()
                                                + "' and ssn="
                                                + ssn.getText();
                                String[] rs = base.executeQuery(reserveExist, 1);
                                if (rs != null) {
                                        JOptionPane.showMessageDialog(reserveFrame,
                                                        "That reservation already exists for that flight");
                                        return;
                                }

                                // must insert into passenger first!
                                String temp = age.getText();
                                if (temp.equals("")) {
                                        temp = "NULL";
                                }
                                
                                //Check that passenger does not exist already
                                String passengerExist = "select l_name from passenger where ssn = "
                                                + ssn.getText();
                                String[] rslt = base.executeQuery(passengerExist, 1);
                                if (rslt == null) {
                                        String passengerInsert = "Insert into passenger(ssn, f_name, l_name, age) values ("
                                                        + ssn.getText()
                                                        + ", "
                                                        + "'"
                                                        + fname.getText()
                                                        + "', '" + lname.getText() + "', " + temp + ")";
                                        System.out.println(passengerInsert);
                                        base.insertIntoDatabase(passengerInsert);
                                        // base.sqlcommit();
                                }

                                //insert the reservation
                                String insert = "Insert into reservation(flight_num, depart_ts, flight_class, amount_paid, "
                                                + "h_phone, o_phone, street, apt_num, state, city, "
                                                + "zip, email, ssn, source_rank, dest_rank) Values ("
                                                + flight[0]
                                                + ", to_timestamp('"
                                                + month
                                                + dayDate.getSelectedItem()
                                                + yearDate.getSelectedItem()
                                                + " "
                                                + flight[4]
                                                + "', 'MMDDYYYY HH24:MI'),"
                                                + flightclass.getSelectedIndex()
                                                + ", "
                                                + paid.getText()
                                                + ", "
                                                + home.getText()
                                                + ", "
                                                + (office.getText().equals("") ? "NULL" : office
                                                                .getText())
                                                + ", '"
                                                + address.getText()
                                                + "', '"
                                                + apt.getText()
                                                + "', '"
                                                + state.getText()
                                                + "', '"
                                                + city.getText()
                                                + "', "
                                                + zip.getText()
                                                + ", '"
                                                + email.getText()
                                                + "', "
                                                + ssn.getText()
                                                + ", " + flight[1] + ", " + flight[2] + ")";
                                //System.out.println(insert);
                                base.insertIntoDatabase(insert);
                                base.sqlcommit();
                                // see if passenger is waiting
                                String stmt = "select reservation_status, priority from reservation where ssn="
                                                + ssn.getText()
                                                + " and flight_num="
                                                + flight[0]
                                                + " and to_char(depart_TS, 'MMDDYYYY') ='"
                                                + month
                                                + dayDate.getSelectedItem()
                                                + yearDate.getSelectedItem() + "'";
                                String[] rs1 = base.executeQuery(stmt, 2);

                                if ((rs1[0].split(","))[0].equals("0")) {
                                        JOptionPane.showMessageDialog(reserveFrame,
                                                        "Flight fulll; Passenger waiting with priority "
                                                                        + rs1[0].split(",")[1]);
                                }

                                frame.setLocation(reserveFrame.getLocation());
                                reserveFrame.dispose();
                                frame.setVisible(true);
                        }
                });
                JButton cancel = new JButton("cancel");
                cancel.addActionListener(new ActionListener() {
                        public void actionPerformed(ActionEvent e) {
                                passengerFrame.setLocation(reserveFrame.getLocation());
                                reserveFrame.dispose();
                                passengerFrame.setVisible(true);
                        }

                });

                reservePanel.add(ssnText);
                reservePanel.add(ssn);
                reservePanel.add(fnameText);
                reservePanel.add(fname);
                reservePanel.add(lnameText);
                reservePanel.add(lname);
                reservePanel.add(ageText);
                reservePanel.add(age);
                reservePanel.add(addressText);
                reservePanel.add(address);
                reservePanel.add(aptText);
                reservePanel.add(apt);
                reservePanel.add(cityText);
                reservePanel.add(city);
                reservePanel.add(stateText);
                reservePanel.add(state);
                reservePanel.add(zipText);
                reservePanel.add(zip);
                reservePanel.add(teleHomeText);
                reservePanel.add(home);
                reservePanel.add(teleOfficeText);
                reservePanel.add(office);
                reservePanel.add(emailText);
                reservePanel.add(email);
                reservePanel.add(classText);
                reservePanel.add(flightclass);
                reservePanel.add(paidText);
                reservePanel.add(paid);
                reservePanel.add(srcText);
                reservePanel.add(source);
                reservePanel.add(dstText);
                reservePanel.add(dst);
                reservePanel.add(dateText);
                reservePanel.add(date);

                reservePanel.add(flightWrapper.getPanel());
                reservePanel.add(findFlight);

                reservePanel.add(submit);
                reservePanel.add(cancel);

                reserveFrame.getContentPane().add(reservePanel);
        }

        /**
         * Checks in a passenger
         */
        private void checkin() {

                NumberFormat ssnFormat = NumberFormat.getIntegerInstance();
                ssnFormat.setMaximumIntegerDigits(9);
                ssnFormat.setMinimumIntegerDigits(9);
                ssnFormat.setGroupingUsed(false);

                final JFrame checkinFrame = new JFrame("Passenger Checkin");
                checkinFrame.setDefaultCloseOperation(JFrame.DO_NOTHING_ON_CLOSE);
                checkinFrame.setSize(500, 500);
                checkinFrame.setResizable(false);
                checkinFrame.setAlwaysOnTop(true);
                checkinFrame.setVisible(true);
                checkinFrame.setLocation(passengerFrame.getLocation());
                passengerFrame.setVisible(false);

                JPanel checkinPanel = new JPanel();
                checkinPanel.setLayout(new GridLayout(0, 2));
                JLabel ssnText = new JLabel("Social Security Number *: ");
                final JFormattedTextField ssn = new JFormattedTextField(ssnFormat);
                JLabel flightText = new JLabel("Flight Number *: ");
                final JFormattedTextField flight = new JFormattedTextField(
                                noCommaFormat);
                JLabel dateText = new JLabel("Date *: ");
                final JComboBox monthDate = new JComboBox(months);
                final JComboBox dayDate = new JComboBox(days);
                final JComboBox yearDate = new JComboBox(years);
                JPanel date = new JPanel();
                date.add(monthDate);
                date.add(dayDate);
                date.add(yearDate);
                JLabel baggageText = new JLabel("Baggage Carried *: ");
                final JFormattedTextField baggage = new JFormattedTextField(noCommaFormat);
                JLabel seatNumText = new JLabel("Seat Number *: ");
                final JTextField seatNum = new JTextField();
                JButton submit = new JButton("Submit");
                submit.addActionListener(new ActionListener() {
                        public void actionPerformed(ActionEvent e) {

                                // check that the date is valid
                                String m = (String) monthDate.getSelectedItem();
                                int d = Integer.parseInt((String) dayDate.getSelectedItem());
                                int y = (Integer) yearDate.getSelectedItem();
                                if (!AirlineGui.validateDate(m, d, y)) {
                                        JOptionPane.showMessageDialog(checkinFrame,
                                                        "Date is invalid");
                                        return;
                                }

                                // make sure important things can't be left blank
                                if (ssn.getText().equals("") || flight.getText().equals("")
                                                || baggage.getText().equals("")
                                                || seatNum.getText().equals("")) {
                                        JOptionPane.showMessageDialog(checkinFrame,
                                                        "One or more required fields were left blank.");
                                        return;
                                }

                                // validate seatNum
                                if (!AirlineGui.validateSeat((String) seatNum.getText())) {
                                        JOptionPane
                                                        .showMessageDialog(checkinFrame,
                                                                        "Seat number must be 2 numbers followed by 1 letter");
                                        return;
                                }

                                String month;
                                if (monthDate.getSelectedIndex() + 1 < 10)
                                        month = "0" + (monthDate.getSelectedIndex() + 1);
                                else
                                        month = "" + (monthDate.getSelectedIndex() + 1);
                                
                                // make sure reservation exists
                                String reservationExist = "select ssn from reservation where ssn="
                                                + ssn.getText()
                                                + " and flight_num="
                                                + flight.getText()
                                                + " and to_char(depart_ts, 'MMDDYYYY')='"
                                                + month
                                                + dayDate.getSelectedItem()
                                                + yearDate.getSelectedItem() + "'";
                                String[] rs = base.executeQuery(reservationExist, 1);
                                if (rs == null) {
                                        JOptionPane
                                                        .showMessageDialog(checkinFrame,
                                                                        "There is no reservation for this person on this flight.");
                                        return;
                                }

                                // check seat not already occupied
                                String checkedInExist = "Select seat_num from reservation where ssn ="
                                                + ssn.getText()
                                                + " and flight_num="
                                                + flight.getText()
                                                + " and to_char(depart_TS, 'MMDDYYYY')='"
                                                + month
                                                + dayDate.getSelectedItem()
                                                + yearDate.getSelectedItem() + "' and checked_in = 1";
                                rs = base.executeQuery(checkedInExist, 1);

                                if (rs != null) {
                                        JOptionPane.showMessageDialog(checkinFrame,
                                                        "This passenger is already checked in: seat "
                                                                        + rs[0]);
                                        return;
                                }
                                // call procedure check_in
                                String checkin = "{call check_in(" + ssn.getText() + ", "
                                                + flight.getText() + ", '" + month
                                                + dayDate.getSelectedItem()
                                                + yearDate.getSelectedItem() + "', "
                                                + baggage.getText() + ", '" + seatNum.getText() + "')}";

                                int worked = base.executeProcedure(checkin);
                                if (worked < 0) {
                                        JOptionPane.showMessageDialog(checkinFrame,
                                                        "That seat is already taken.");
                                        return;
                                }
                                base.sqlcommit();
                                frame.setLocation(checkinFrame.getLocation());
                                checkinFrame.dispose();
                                frame.setVisible(true);

                        }
                });
                JButton cancel = new JButton("cancel");
                cancel.addActionListener(new ActionListener() {
                        public void actionPerformed(ActionEvent e) {
                                passengerFrame.setLocation(checkinFrame.getLocation());
                                checkinFrame.dispose();
                                passengerFrame.setVisible(true);
                        }

                });

                checkinPanel.add(ssnText);
                checkinPanel.add(ssn);
                checkinPanel.add(flightText);
                checkinPanel.add(flight);
                checkinPanel.add(dateText);
                checkinPanel.add(date);
                checkinPanel.add(baggageText);
                checkinPanel.add(baggage);
                checkinPanel.add(seatNumText);
                checkinPanel.add(seatNum);
                checkinPanel.add(submit);
                checkinPanel.add(cancel);

                checkinFrame.getContentPane().add(checkinPanel);
        }

        /**
         * Deletes a reservation
         */
        private void deleteReservation() {

                NumberFormat ssnFormat = NumberFormat.getIntegerInstance();
                ssnFormat.setMaximumIntegerDigits(9);
                ssnFormat.setMinimumIntegerDigits(9);
                ssnFormat.setGroupingUsed(false);

                final JFrame delFrame = new JFrame("Cancel Reservation");
                delFrame.setDefaultCloseOperation(JFrame.DO_NOTHING_ON_CLOSE);
                delFrame.setSize(500, 500);
                delFrame.setResizable(false);
                delFrame.setAlwaysOnTop(true);
                delFrame.setVisible(true);
                delFrame.setLocation(passengerFrame.getLocation());
                passengerFrame.setVisible(false);

                JPanel delPanel = new JPanel();
                delPanel.setLayout(new BoxLayout(delPanel, BoxLayout.PAGE_AXIS));
                JLabel ssnText = new JLabel("Social Security Number: ");
                final JFormattedTextField ssn = new JFormattedTextField(ssnFormat);
                ssn.setColumns(10);
                JLabel flightText = new JLabel("Flight Number: ");
                final JFormattedTextField flight = new JFormattedTextField(
                                noCommaFormat);
                flight.setColumns(10);
                JLabel dateText = new JLabel("Date: ");
                final JComboBox monthDate = new JComboBox(months);
                final JComboBox dayDate = new JComboBox(days);
                final JComboBox yearDate = new JComboBox(years);
                JPanel date = new JPanel();
                date.add(dateText);
                date.add(monthDate);
                date.add(dayDate);
                date.add(yearDate);

                JButton submit = new JButton("Submit");
                submit.addActionListener(new ActionListener() {
                        public void actionPerformed(ActionEvent e) {
                                // call method to send to database
                                String m = (String) monthDate.getSelectedItem();
                                int d = Integer.parseInt((String) dayDate.getSelectedItem());
                                int y = (Integer) yearDate.getSelectedItem();
                                if (!AirlineGui.validateDate(m, d, y)) {
                                        JOptionPane.showMessageDialog(delFrame, "Date is invalid");
                                        return;
                                }
                                // make sure important things can't be left blank
                                if (ssn.getText().equals("") || flight.getText().equals("")) {
                                        JOptionPane.showMessageDialog(delFrame,
                                                        "One or more required fields were left blank.");
                                        return;
                                }

                                String month;
                                if (monthDate.getSelectedIndex() + 1 < 10)
                                        month = "0" + (monthDate.getSelectedIndex() + 1);
                                else
                                        month = "" + (monthDate.getSelectedIndex() + 1);
                                // check that the reservation exists
                                String resExist = "select ssn from reservation where flight_num = "
                                                + flight.getText()
                                                + " and to_char(depart_ts, 'MMDDYYYY') =  '"
                                                + month
                                                + dayDate.getSelectedItem()
                                                + yearDate.getSelectedItem()
                                                + "' and ssn = "
                                                + ssn.getText();
                                System.out.println(resExist);
                                String[] rs = base.executeQuery(resExist, 1);
                                if (rs == null) {
                                        JOptionPane.showMessageDialog(delFrame,
                                                        "That reservation does not exist");
                                        return;
                                }
                                
                                // Procedure to cancel the reservation
                                String delRes = "{call cancel_reservation( " + flight.getText()
                                                + ", '" + month + dayDate.getSelectedItem()
                                                + yearDate.getSelectedItem() + "', " + ssn.getText()
                                                + ")}";
                                base.executeProcedure(delRes);
                                base.sqlcommit();
                                frame.setLocation(delFrame.getLocation());
                                delFrame.dispose();
                                frame.setVisible(true);
                        }
                });
                JButton cancel = new JButton("cancel");
                cancel.addActionListener(new ActionListener() {
                        public void actionPerformed(ActionEvent e) {
                                passengerFrame.setLocation(delFrame.getLocation());
                                delFrame.dispose();
                                passengerFrame.setVisible(true);
                        }

                });

                JPanel ssnPanel = new JPanel();
                ssnPanel.add(ssnText);
                ssnPanel.add(ssn);
                JPanel flightPanel = new JPanel();
                flightPanel.add(flightText);
                flightPanel.add(flight);
                JPanel buttons = new JPanel();
                buttons.add(submit);
                buttons.add(cancel);

                delPanel.add(ssnPanel);
                delPanel.add(flightPanel);
                delPanel.add(date);
                delPanel.add(buttons);

                delFrame.getContentPane().add(delPanel);
        }

        private void schedule() {
                final JFrame scheduleFrame = new JFrame("Schedule Flight");
                scheduleFrame.setDefaultCloseOperation(JFrame.DO_NOTHING_ON_CLOSE);
                scheduleFrame.setSize(500, 500);
                scheduleFrame.setResizable(false);
                scheduleFrame.setAlwaysOnTop(true);
                scheduleFrame.setVisible(true);
                scheduleFrame.setLocation(aircraftFrame.getLocation());
                aircraftFrame.setVisible(false);

                final NumberFormat timeFormat = NumberFormat.getIntegerInstance();
                timeFormat.setMaximumIntegerDigits(4);
                timeFormat.setMinimumIntegerDigits(4);
                timeFormat.setGroupingUsed(false);

                JPanel schedulePanel = new JPanel();
                schedulePanel.setLayout(new GridBagLayout());
                JLabel flightText = new JLabel("Flight Number *: ");
                final JFormattedTextField flight = new JFormattedTextField(
                                noCommaFormat);
                JLabel dateText = new JLabel("Date: ");

                final JComboBox monthDate = new JComboBox(months);
                final JComboBox dayDate = new JComboBox(days);
                final JComboBox yearDate = new JComboBox(years);
                JPanel date = new JPanel();
                date.add(monthDate);
                date.add(dayDate);
                date.add(yearDate);
                JLabel typeText = new JLabel("Aircraft Type: ");
                final JPanel sourcePanel = new JPanel();
                final JPanel destPanel = new JPanel();
                sourcePanel.setLayout(new GridLayout(0, 2));
                destPanel.setLayout(new GridLayout(0, 2));
                final JComboBox type = new JComboBox(aircraftType);
                JLabel sourceText = new JLabel("Source City *: ");
                JLabel dstText = new JLabel("Destination City *: ");
                JLabel dTimeText = new JLabel("Departure Time (ex 1345)*: ");
                final JFormattedTextField dtime = new JFormattedTextField(timeFormat);
                JLabel arriveText = new JLabel("Arrival Time (ex 1345)*: ");
                final JFormattedTextField arrive = new JFormattedTextField(timeFormat);

                final ComboBoxWrapper cityCombos = new ComboBoxWrapper();

                final PanelWrapper moreLegs = new PanelWrapper();

                JComboBox sourceCities = new JComboBox(cities);
                JComboBox destCities = new JComboBox(cities);

                sourceCities.setSelectedItem(null);
                destCities.setSelectedItem(null);

                final ActionListener cityListener = new ActionListener() {
                        public void actionPerformed(ActionEvent e) {
                                ComboBoxWrapper.remove((String) ((JComboBox) e.getSource())
                                                .getSelectedItem());
                        }
                };

                sourceCities.addActionListener(cityListener);
                destCities.addActionListener(cityListener);
                final int source_idx = cityCombos.addComboBox(sourceCities);
                final int dest_idx = cityCombos.addComboBox(destCities);

                sourcePanel.add(sourceText);
                sourcePanel.add(dTimeText);
                sourcePanel.add(cityCombos.getComboBox(source_idx));
                sourcePanel.add(dtime);
                destPanel.add(dstText);
                destPanel.add(arriveText);
                destPanel.add(cityCombos.getComboBox(dest_idx));
                destPanel.add(arrive);

                JButton addCity = new JButton("Click to add destination");
                final JButton removeCity = new JButton("Click to remove destination");

                removeCity.addActionListener(new ActionListener() {
                        public void actionPerformed(ActionEvent e) {
                                if (moreLegs.getCount() == 1) {
                                        removeCity.setVisible(false);
                                        removeCity.setEnabled(false);
                                }
                                cityCombos.removeComboBox();
                                moreLegs.removeLeg();
                                scheduleFrame.validate();
                                scheduleFrame.repaint();
                        }
                });

                addCity.addActionListener(new ActionListener() {
                        // adds intermediary cities
                        public void actionPerformed(ActionEvent e) {
                                JPanel nextLeg = new JPanel(new GridLayout(0, 3));
                                if (moreLegs.count == 0) {
                                        nextLeg.add(new JLabel("Departure Time:"));
                                        nextLeg.add(new JLabel("Destination"));
                                        nextLeg.add(new JLabel("Arrival Time:"));
                                } else {
                                        nextLeg.add(new JLabel(""));
                                        nextLeg.add(new JLabel(""));
                                        nextLeg.add(new JLabel(""));
                                }
                                nextLeg.add(new JFormattedTextField(timeFormat));
                                JComboBox city = new JComboBox(cities);
                                city.setSelectedItem(null);
                                city.addActionListener(cityListener);
                                int city_idx = cityCombos.addComboBox(city);
                                nextLeg.add(cityCombos.getComboBox(city_idx));
                                nextLeg.add(new JFormattedTextField(timeFormat));
                                moreLegs.add(nextLeg);
                                removeCity.setVisible(true);
                                removeCity.setEnabled(true);
                                scheduleFrame.validate();
                                scheduleFrame.repaint();
                        }
                });

                JButton submit = new JButton("Submit");
                submit.addActionListener(new ActionListener() {
                        public void actionPerformed(ActionEvent e) {
                                String m = (String) monthDate.getSelectedItem();
                                int d = Integer.parseInt((String) dayDate.getSelectedItem());
                                int y = (Integer) yearDate.getSelectedItem();
                                if (!AirlineGui.validateDate(m, d, y)) {
                                        JOptionPane.showMessageDialog(scheduleFrame,
                                                        "Date is invalid");
                                        return;
                                }

                                // make sure important things can't be left blank
                                if (flight.getText().equals("")
                                                || cityCombos.getComboBox(source_idx).getSelectedItem() == null
                                                || cityCombos.getComboBox(dest_idx).getSelectedItem() == null
                                                || (String) dtime.getText() == null
                                                || (String) arrive.getText() == null) {
                                        JOptionPane.showMessageDialog(scheduleFrame,
                                                        "One or more required fields were left blank.");
                                        return;
                                }

                                if (!AirlineGui.validateTime(Integer.parseInt((String) arrive
                                                .getText()))
                                                || !AirlineGui.validateTime(Integer
                                                                .parseInt((String) dtime.getText()))) {
                                        JOptionPane.showMessageDialog(scheduleFrame,
                                                        "Time is invalid");
                                        return;
                                }
                                String month;
                                if (monthDate.getSelectedIndex() < 10)
                                        month = "0" + (monthDate.getSelectedIndex() + 1);
                                else
                                        month = "" + (monthDate.getSelectedIndex() + 1);
                                String flightExist = "select source from flight where flight_num = "
                                                + flight.getText()
                                                + " and to_char(depart_ts, 'MMDDYYYY') =  '"
                                                + month
                                                + dayDate.getSelectedItem()
                                                + yearDate.getSelectedItem() + "'";
                                String[] rs = base.executeQuery(flightExist, 1);
                                if (rs != null) {
                                        JOptionPane.showMessageDialog(scheduleFrame,
                                                        "That flight number exists already for that day");
                                        return;
                                }
                                int mIdx = monthDate.getSelectedIndex();
                                String mm = mIdx > 10 ? Integer.toString(mIdx) : "0"
                                                + Integer.toString(mIdx + 1);

                                int prevTime;

                                ArrayList flightLegs = new ArrayList();
                                String leg[] = new String[3];
                                // city
                                leg[1] = (String) cityCombos.getComboBox(source_idx)
                                                .getSelectedItem();
                                // depart from city
                                leg[2] = (String) dtime.getText();
                                prevTime = Integer.parseInt((String) dtime.getText());
                                flightLegs.add(leg);

                                leg = new String[3];
                                // city
                                leg[1] = (String) cityCombos.getComboBox(dest_idx)
                                                .getSelectedItem();
                                // arrive at city
                                leg[0] = (String) arrive.getText();
                                if (prevTime >= Integer.parseInt(leg[0])) {
                                        JOptionPane.showMessageDialog(scheduleFrame,
                                                        "Chronological problem.");
                                        return;
                                } else {
                                        prevTime = Integer.parseInt(leg[0]);
                                }
                                flightLegs.add(leg);

                                ArrayList restOfRoute = moreLegs.getPanelLocations();

                                if (restOfRoute != null) {

                                        for (int i = 0; i < restOfRoute.size(); i++) {
                                                leg = ((String) restOfRoute.get(i)).split(",");
                                                if (leg.length < 3 || leg[0] == null || leg[1] == null
                                                                || leg[2] == null) {
                                                        JOptionPane
                                                                        .showMessageDialog(scheduleFrame,
                                                                                        "One or more required fields were left blank.");
                                                        return;
                                                } else if (!AirlineGui.validateTime(Integer
                                                                .parseInt(leg[0]))
                                                                || !AirlineGui.validateTime(Integer
                                                                                .parseInt(leg[2]))) {
                                                        JOptionPane.showMessageDialog(scheduleFrame,
                                                                        "One or more time fields are invalid");
                                                        return;
                                                }

                                                if (prevTime >= Integer.parseInt(leg[0])) {
                                                        JOptionPane.showMessageDialog(scheduleFrame,
                                                                        "Chronological problem");
                                                        return;
                                                } else {
                                                        prevTime = Integer.parseInt(leg[0]);
                                                }

                                                if (prevTime >= Integer.parseInt(leg[2])) {
                                                        JOptionPane.showMessageDialog(scheduleFrame,
                                                                        "Chronological Problem");
                                                        return;
                                                } else {
                                                        prevTime = Integer.parseInt(leg[2]);
                                                }

                                                flightLegs.add(leg);

                                        }
                                }

                                String insert;
                                String dateString = mm
                                                + dayDate.getSelectedItem()
                                                + Integer
                                                                .toString((Integer) yearDate.getSelectedItem());
                                int rank = 0;
                                while (rank + 1 < flightLegs.size()) {
                                        String depart_TS = "";
                                        String arrive_TS = "";
                                        if (rank == 0) {
                                                depart_TS = "to_timestamp('" + dateString + " "
                                                                + ((String[]) flightLegs.get(rank))[2]
                                                                + "', 'MMDDYYYY HH24MI')";
                                                arrive_TS = "to_timestamp('" + dateString + " "
                                                                + ((String[]) flightLegs.get(rank + 1))[0]
                                                                + "', 'MMDDYYYY HH24MI')";
                                        } else {
                                                depart_TS = "to_timestamp('" + dateString + " "
                                                                + ((String[]) flightLegs.get(rank + 1))[0]
                                                                + "', 'MMDDYYYY HH24MI')";
                                                arrive_TS = "to_timestamp('" + dateString + " "
                                                                + ((String[]) flightLegs.get(rank + 1))[2]
                                                                + "', 'MMDDYYYY HH24MI')";
                                        }
                                        insert = "insert into flight(flight_num, source, destination, source_rank, dest_rank, arrive_TS, depart_TS) values ("
                                                        + flight.getText()
                                                        + ", '"
                                                        + ((String[]) flightLegs.get(rank))[1]
                                                        + "', '"
                                                        + ((String[]) flightLegs.get(rank + 1))[1]
                                                        + "', "
                                                        + rank
                                                        + ", "
                                                        + (rank + 1)
                                                        + ", "
                                                        + arrive_TS
                                                        + ", " + depart_TS + ")";

                                        System.out.println(insert);
                                        rank++;
                                        base.insertIntoDatabase(insert);
                                }

                                insert = "Insert into flight_plane(aircraft_type, max_occupancy, flight_num, depart_TS) values ('"
                                                + type.getSelectedItem()
                                                + "', "
                                                + occupancy[type.getSelectedIndex()]
                                                + ", "
                                                + flight.getText()
                                                + ", "
                                                + "to_timestamp('"
                                                + dateString
                                                + " "
                                                + ((String[]) flightLegs.get(0))[2]
                                                + "', 'MMDDYYYY HH24MI'))";
                                System.out.println(insert);
                                base.insertIntoDatabase(insert);
                                base.sqlcommit();

                                frame.setLocation(scheduleFrame.getLocation());
                                scheduleFrame.dispose();
                                frame.setVisible(true);
                        }
                });
                JButton cancel = new JButton("cancel");
                cancel.addActionListener(new ActionListener() {
                        public void actionPerformed(ActionEvent e) {
                                aircraftFrame.setLocation(scheduleFrame.getLocation());
                                scheduleFrame.dispose();
                                aircraftFrame.setVisible(true);
                        }
                });

                int row = 0;
                GridBagConstraints c = new GridBagConstraints();
                c.fill = GridBagConstraints.HORIZONTAL;
                c.gridx = 0;
                c.gridy = row++;
                c.weightx = 0.5;
                c.weighty = 0.5;
                schedulePanel.add(flightText, c);
                c.gridx = GridBagConstraints.RELATIVE;
                schedulePanel.add(flight, c);
                c.gridx = 0;
                c.gridy = row++;
                schedulePanel.add(dateText, c);
                c.gridx = GridBagConstraints.RELATIVE;
                schedulePanel.add(date, c);

                c.gridx = 0;
                c.gridy = row++;
                schedulePanel.add(typeText, c);
                c.gridx = GridBagConstraints.RELATIVE;
                schedulePanel.add(type, c);
                c.gridx = 0;
                c.gridy = row++;
                c.gridwidth = 2;
                schedulePanel.add(sourcePanel, c);

                c.gridx = 0;
                c.gridy = row++;
                schedulePanel.add(destPanel, c);
                c.gridx = 0;
                c.gridy = row++;
                schedulePanel.add(moreLegs.getPanel(), c);

                c.anchor = GridBagConstraints.PAGE_END;
                c.gridx = 0;
                c.gridy = row++;
                c.gridwidth = 1;
                schedulePanel.add(addCity, c);
                c.gridx = GridBagConstraints.RELATIVE;
                removeCity.setVisible(false);
                removeCity.setEnabled(false);
                schedulePanel.add(removeCity, c);

                c.gridx = 0;
                c.gridy = row++;
                schedulePanel.add(submit, c);
                c.gridx = GridBagConstraints.RELATIVE;
                schedulePanel.add(cancel, c);
                scheduleFrame.getContentPane().add(schedulePanel);

        }

        private void update() {
                final JFrame updateFrame = new JFrame("Update Flight");
                updateFrame.setDefaultCloseOperation(JFrame.DO_NOTHING_ON_CLOSE);
                updateFrame.setSize(500, 500);
                updateFrame.setResizable(false);
                updateFrame.setAlwaysOnTop(true);
                updateFrame.setVisible(true);
                updateFrame.setLocation(aircraftFrame.getLocation());
                aircraftFrame.setVisible(false);

                String[] months = { "Jan", "Feb", "Mar", "April", "May", "June",
                                "July", "Aug", "Sept", "Oct", "Nov", "Dec" };

                NumberFormat timeFormat = NumberFormat.getIntegerInstance();
                timeFormat.setMaximumIntegerDigits(4);
                timeFormat.setMinimumIntegerDigits(4);
                timeFormat.setGroupingUsed(false);

                ButtonGroup radioGroup = new ButtonGroup();

                JPanel updatePanel = new JPanel();
                updatePanel.setLayout(new GridLayout(0, 2));
                JLabel flightText = new JLabel("Flight Number: ");
                final JFormattedTextField flight = new JFormattedTextField(
                                noCommaFormat);
                JButton find = new JButton("Find Cities");
                JLabel dstText = new JLabel("Destination City: ");
                final JComboBox dst = new JComboBox();

                JLabel choose = new JLabel("Change Departure or Arrival Schedule");
                final JRadioButton depart = new JRadioButton("Departure");
                final JRadioButton arrive = new JRadioButton("Arrive");
                depart.setEnabled(false);
                arrive.setEnabled(false);
                radioGroup.add(depart);
                radioGroup.add(arrive);
                JLabel spacer = new JLabel("");
                JLabel dateText = new JLabel("Date: ");
                final JComboBox monthDate = new JComboBox(months);
                final JComboBox dayDate = new JComboBox(days);
                final JComboBox yearDate = new JComboBox(years);
                JPanel date = new JPanel();
                date.add(monthDate);
                date.add(dayDate);
                date.add(yearDate);
                JLabel dTimeText = new JLabel("Time ex. 1345: ");
                final JFormattedTextField dtime = new JFormattedTextField(timeFormat);

                JButton submit = new JButton("Submit");
                find.addActionListener(new ActionListener() {
                        public void actionPerformed(ActionEvent e) {
                                String m = (String) monthDate.getSelectedItem();
                                int d = Integer.parseInt((String) dayDate.getSelectedItem());
                                int y = (Integer) yearDate.getSelectedItem();
                                if (!AirlineGui.validateDate(m, d, y)) {
                                        JOptionPane.showMessageDialog(updateFrame,
                                                        "Date is invalid");
                                        return;
                                }

                                // make sure important things can't be left blank
                                if (flight.getText().equals("")) {
                                        JOptionPane.showMessageDialog(updateFrame,
                                                        "One or more required fields were left blank.");
                                        return;
                                }

                                // if(!AirlineGui.validateTime(Integer.parseInt((String)dtime.
                                // getText())))
                                // {
                                // JOptionPane.showMessageDialog(updateFrame,
                                // "Time is invalid");
                                // return;
                                // }
                                dst.removeAllItems();
                                String month;
                                if (monthDate.getSelectedIndex() + 1 < 10)
                                        month = "0" + (monthDate.getSelectedIndex() + 1);
                                else
                                        month = "" + (monthDate.getSelectedIndex() + 1);
                                String choice;
                                
                                String sql = "Select source, destination from flight where flight_num=" +
                                flight.getText() + " and to_char(depart_ts, 'MMDDYYYY')='" + 
                                month + dayDate.getSelectedItem() + yearDate.getSelectedItem() + "' order by source_rank";

                                final String[] locs = base.executeQuery(sql, 2);
                                if (locs == null) {
                                        dst.removeAllItems();
                                        dst.addItem("No flights found");
                                        dst.setEnabled(false);
                                } else {
                                        dst.setEnabled(true);
                                        dst.removeAllItems();
                                        for (int i = 0; i < locs.length; i++) {
                                                if(i == 0)
                                                {
                                                        dst.addItem(locs[i].split(",")[0]);
                                                }
                                                dst.addItem(locs[i].split(",")[1]);
                                                dst.setSelectedItem(null);
                                        }
                                        
                                        dst.addActionListener(new ActionListener() {
                                                public void actionPerformed(ActionEvent e) {
                                                                System.out.println(dst.getSelectedIndex());
                                                                System.out.println(locs.length);
                                                                if(dst.getSelectedIndex() == -1)
                                                                {
                                                                        
                                                                }
                                                                else if(dst.getSelectedIndex() == 0)
                                                                {
                                                                        depart.setEnabled(true);
                                                                        depart.setSelected(true);
                                                                        arrive.setEnabled(false);
                                                                
                                                                }
                                                                else if (dst.getSelectedIndex() == locs.length)
                                                                {
                                                                        arrive.setSelected(true);
                                                                        depart.setEnabled(false);
                                                                        
                                                                }
                                                                else
                                                                {
                                                                        arrive.setEnabled(true);
                                                                        depart.setEnabled(true);
                                                                }
                                                                
                                                }
                                        });
                                        
                                }

                        }
                });
                submit.addActionListener(new ActionListener() {
                        public void actionPerformed(ActionEvent e) {

                                String m = (String) monthDate.getSelectedItem();
                                int d = Integer.parseInt((String) dayDate.getSelectedItem());
                                int y = (Integer) yearDate.getSelectedItem();
                                if (!AirlineGui.validateDate(m, d, y)) {
                                        JOptionPane.showMessageDialog(updateFrame,
                                                        "Date is invalid");
                                        return;
                                }

                                if (flight.getText().equals("")
                                                || dst.getSelectedItem() == null
                                                || ((String) dtime.getText()).equals("")) {
                                        JOptionPane.showMessageDialog(updateFrame,
                                                        "One or more required fields were left blank.");
                                        return;
                                }
                                if (!AirlineGui.validateTime(Integer.parseInt((String) dtime
                                                .getText()))) {
                                        JOptionPane.showMessageDialog(updateFrame,
                                                        "Time is invalid");
                                        return;
                                }
                                if (dst.getSelectedItem().equals("No flights found")) {
                                        JOptionPane.showMessageDialog(updateFrame,
                                                        "You need a valid flight.");
                                        return;
                                }
                                // call method to send to database
                                String update;
                                String choice;
                                String loc;
                                String month;
                                if (monthDate.getSelectedIndex() + 1 < 10)
                                        month = "0" + (monthDate.getSelectedIndex() + 1);
                                else
                                        month = "" + (monthDate.getSelectedIndex() + 1);
                                if (depart.isSelected()) {
                                        choice = "depart_ts";
                                        loc = "source";
                                } else {
                                        choice = "arrive_ts";
                                        loc = "destination";
                                }
                                update = "Update flight set " + choice + " = To_Timestamp('"
                                                + month + dayDate.getSelectedItem()
                                                + yearDate.getSelectedItem() + dtime.getText()
                                                + "', 'MMDDyyyyhh24mi')" + " where flight_num = "
                                                + flight.getText() + " and " + loc + " = '"
                                                + dst.getSelectedItem() + "' and to_char(depart_ts, 'MMDDYYYY') = '" + month + dayDate.getSelectedItem()
                                                + yearDate.getSelectedItem() + "'";
                                System.out.println(update);
                                int worked = base.executeUpdate(update);
                                if(worked < 0)
                                {
                                        JOptionPane.showMessageDialog(updateFrame, "Operation failed.  It is possible this flight is leaving another location at this time.");
                                        return;
                                }
                                base.sqlcommit();
                                frame.setLocation(updateFrame.getLocation());
                                updateFrame.dispose();
                                frame.setVisible(true);
                        }
                });
                JButton cancel = new JButton("cancel");
                cancel.addActionListener(new ActionListener() {
                        public void actionPerformed(ActionEvent e) {
                                aircraftFrame.setLocation(updateFrame.getLocation());
                                updateFrame.dispose();
                                aircraftFrame.setVisible(true);
                        }
                });

                updatePanel.add(flightText);
                updatePanel.add(flight);
                updatePanel.add(dateText);
                updatePanel.add(date);
                updatePanel.add(new Panel());
                updatePanel.add(find);
                updatePanel.add(dstText);
                updatePanel.add(dst);
                updatePanel.add(choose);
                updatePanel.add(spacer);
                updatePanel.add(depart);
                updatePanel.add(arrive);
                updatePanel.add(dTimeText);
                updatePanel.add(dtime);

                updatePanel.add(submit);
                updatePanel.add(cancel);

                updateFrame.getContentPane().add(updatePanel);

        }

        private void postReport() {

                final JFrame postFrame = new JFrame("Post Flight Report");
                postFrame.setDefaultCloseOperation(JFrame.DO_NOTHING_ON_CLOSE);
                postFrame.setSize(500, 500);
                postFrame.setResizable(false);
                postFrame.setAlwaysOnTop(true);
                postFrame.setVisible(true);
                postFrame.setLocation(aircraftFrame.getLocation());
                aircraftFrame.setVisible(false);

                JPanel postPanel = new JPanel();
                postPanel.setLayout(new GridLayout(0, 2));
                JLabel flightText = new JLabel("Flight Number: ");
                final JFormattedTextField flight = new JFormattedTextField(
                                noCommaFormat);
                JLabel dateText = new JLabel("Date: ");
                final JComboBox monthDate = new JComboBox(months);
                final JComboBox dayDate = new JComboBox(days);
                final JComboBox yearDate = new JComboBox(years);
                JPanel date = new JPanel();
                date.add(monthDate);
                date.add(dayDate);
                date.add(yearDate);
                JButton find = new JButton("Find Cities");
                JLabel srcText = new JLabel("Source City:");
                JLabel dstText = new JLabel("Destination City: ");
                final JComboBox dst = new JComboBox();
                final JComboBox src = new JComboBox();
                find.addActionListener(new ActionListener() {
                        public void actionPerformed(ActionEvent e) {
                                String month;
                                if (monthDate.getSelectedIndex() + 1 < 10)
                                        month = "0" + (monthDate.getSelectedIndex() + 1);
                                else
                                        month = "" + (monthDate.getSelectedIndex() + 1);
                                String sql = "Select source, destination from flight where flight_num = "
                                                + flight.getText()
                                                + " and to_char(depart_ts, 'MMDDYYYY') = '"
                                                + month
                                                + dayDate.getSelectedItem()
                                                + yearDate.getSelectedItem() + "' order by dest_rank";
                                String[] dest = base.executeQuery(sql, 2);
                                if (dest == null) {
                                        src.removeAllItems();
                                        dst.removeAllItems();
                                        src.addItem("No flights found");
                                        dst.addItem("No flights found");
                                        dst.setEnabled(false);
                                        src.setEnabled(false);
                                } else {
                                        src.setEnabled(true);
                                        dst.setEnabled(true);
                                        src.removeAllItems();
                                        dst.removeAllItems();
                                        for (int i = 0; i < dest.length; i++) {
                                                src.addItem(dest[i].split(",")[0]);
                                                dst.addItem(dest[i].split(",")[1]);
                                        }

                                        // dst.removeAllItems();
                                        // for (int i = 0; i < dest.length; i++) {
                                        // dst.addItem(dest[i]);
                                        // }
                                }

                        }
                });
                // final JComboBox dst = new JComboBox(cities);

                JButton submit = new JButton("Submit");
                submit.addActionListener(new ActionListener() {
                        public void actionPerformed(ActionEvent e) {
                                String m = (String) monthDate.getSelectedItem();
                                int d = Integer.parseInt((String) dayDate.getSelectedItem());
                                int y = (Integer) yearDate.getSelectedItem();
                                if (!AirlineGui.validateDate(m, d, y)) {
                                        JOptionPane.showMessageDialog(postFrame, "Date is invalid");
                                        return;
                                }

                                if (flight.getText().equals("")
                                                || dst.getSelectedItem() == null) {
                                        JOptionPane.showMessageDialog(postFrame,
                                                        "One or more required fields were left blank.");
                                        return;
                                }
                                if (dst.getSelectedItem().equals("No flights found")) {
                                        JOptionPane.showMessageDialog(postFrame,
                                                        "You need a valid flight.");
                                        return;
                                }

                                if (src.getSelectedIndex() >= dst.getSelectedIndex() + 1) {
                                        JOptionPane.showMessageDialog(postFrame,
                                                        "Invalid source/destination combination");
                                        return;
                                }
                                // call method to get report from database
                                String month;
                                if (monthDate.getSelectedIndex() + 1 < 10)
                                        month = "0" + (monthDate.getSelectedIndex() + 1);
                                else
                                        month = "" + (monthDate.getSelectedIndex() + 1);
                                String onBoard = "{call ?:=countOnBoard(" + flight.getText()
                                                + ", '" + month + dayDate.getSelectedItem()
                                                + yearDate.getSelectedItem() + "',"
                                                + src.getSelectedIndex() + ", "
                                                + (dst.getSelectedIndex() + 1) + ")}";
                                String fDur = "{call ?:=flightDuration( " + flight.getText()
                                                + ", '" + month + dayDate.getSelectedItem()
                                                + yearDate.getSelectedItem() + "', "
                                                + src.getSelectedIndex() + ", "
                                                + (dst.getSelectedIndex() + 1) + ")}";
                                // only going to one place is "nonstop"
                                System.out.println(onBoard + "\n" + fDur);
                                int numbStops = dst.getSelectedIndex() - src.getSelectedIndex();
                                int numBoard = base.executeFunction(onBoard);
                                int duration = base.executeFunction(fDur);
                                // try {
                                // numBoard = base.executeQuery(onBoard).getInt(1);
                                // duration = base.executeQuery(fDur).getInt(1);
                                // } catch (SQLException e1) {
                                // // TODO Auto-generated catch block
                                // e1.printStackTrace();
                                // }

                                final JFrame reportFrame = new JFrame(flight.getText()
                                                + " Post Report");
                                reportFrame
                                                .setDefaultCloseOperation(JFrame.DO_NOTHING_ON_CLOSE);
                                reportFrame.setSize(500, 500);
                                reportFrame.setResizable(false);
                                reportFrame.setAlwaysOnTop(true);
                                reportFrame.setLocation(postFrame.getLocation());
                                reportFrame.setVisible(true);

                                JPanel reportPanel = new JPanel();
                                reportPanel.setLayout(new BoxLayout(reportPanel,
                                                BoxLayout.Y_AXIS));
                                JLabel numPassengers = new JLabel("Number of Passengers: "
                                                + numBoard);
                                JLabel numStops = new JLabel("Number of Stops: " + numbStops);
                                JLabel time = new JLabel("Total Travel Time (Minutes): "
                                                + duration);
                                JButton ok = new JButton("OK");
                                ok.addActionListener(new ActionListener() {
                                        public void actionPerformed(ActionEvent e) {
                                                frame.setLocation(reportFrame.getLocation());
                                                reportFrame.dispose();
                                                frame.setVisible(true);
                                        }
                                });
                                reportPanel.add(numPassengers);
                                reportPanel.add(numStops);
                                reportPanel.add(time);
                                reportPanel.add(ok);

                                reportFrame.getContentPane().add(reportPanel);

                                postFrame.dispose();
                        }
                });
                JButton cancel = new JButton("cancel");
                cancel.addActionListener(new ActionListener() {
                        public void actionPerformed(ActionEvent e) {
                                aircraftFrame.setLocation(postFrame.getLocation());
                                postFrame.dispose();
                                aircraftFrame.setVisible(true);
                        }
                });

                postPanel.add(flightText);
                postPanel.add(flight);
                postPanel.add(dateText);
                postPanel.add(date);
                postPanel.add(new Panel());
                postPanel.add(find);
                postPanel.add(srcText);
                postPanel.add(src);
                postPanel.add(dstText);
                postPanel.add(dst);

                postPanel.add(submit);
                postPanel.add(cancel);

                postFrame.getContentPane().add(postPanel);
        }

        private void deleteFlight() {
                final JFrame delFrame = new JFrame("Cancel Flight");
                delFrame.setDefaultCloseOperation(JFrame.DO_NOTHING_ON_CLOSE);
                delFrame.setSize(500, 500);
                delFrame.setResizable(false);
                delFrame.setAlwaysOnTop(true);
                delFrame.setVisible(true);
                delFrame.setLocation(aircraftFrame.getLocation());
                aircraftFrame.setVisible(false);

                NumberFormat timeFormat = NumberFormat.getIntegerInstance();
                timeFormat.setMaximumIntegerDigits(4);
                timeFormat.setMinimumIntegerDigits(4);
                timeFormat.setGroupingUsed(false);

                JPanel delPanel = new JPanel();
                delPanel.setLayout(new BoxLayout(delPanel, 3));
                JLabel flightText = new JLabel("Flight Number: ");
                final JFormattedTextField flight = new JFormattedTextField(
                                noCommaFormat);
                flight.setColumns(10);
                JLabel dateText = new JLabel("Date: ");
                final JComboBox monthDate = new JComboBox(months);
                final JComboBox dayDate = new JComboBox(days);
                final JComboBox yearDate = new JComboBox(years);
                JPanel date = new JPanel();
                date.add(dateText);
                date.add(monthDate);
                date.add(dayDate);
                date.add(yearDate);

                JButton submit = new JButton("Submit");
                submit.addActionListener(new ActionListener() {
                        public void actionPerformed(ActionEvent e) {
                                String m = (String) monthDate.getSelectedItem();
                                int d = Integer.parseInt((String) dayDate.getSelectedItem());
                                int y = (Integer) yearDate.getSelectedItem();
                                if (!AirlineGui.validateDate(m, d, y)) {
                                        JOptionPane.showMessageDialog(delFrame, "Date is invalid");
                                        return;
                                }
                                if (flight.getText().equals("")) {
                                        JOptionPane.showMessageDialog(delFrame,
                                                        "One or more required fields were left blank.");
                                        return;
                                }
                                String month;
                                if (monthDate.getSelectedIndex() < 10)
                                        month = "0" + (monthDate.getSelectedIndex() + 1);
                                else
                                        month = "" + (monthDate.getSelectedIndex() + 1);
                                String flightExist = "select source from flight where flight_num = "
                                                + flight.getText()
                                                + " and to_char(depart_ts, 'MMDDYYYY') =  '"
                                                + month
                                                + dayDate.getSelectedItem()
                                                + yearDate.getSelectedItem() + "'";
                                String[] rs = base.executeQuery(flightExist, 1);
                                if (rs == null) {
                                        JOptionPane.showMessageDialog(delFrame,
                                                        "That flight does not exist");
                                        return;
                                }
                                /*
                                 * String deletefp =
                                 * "Delete from flight_plane where flight_num = " +
                                 * flight.getText() + " and to_char(depart_ts, 'MMDDYYYY') = " +
                                 * month + dayDate.getSelectedItem() +
                                 * yearDate.getSelectedItem(); base.executeUpdate(deletefp);
                                 */
                                // call method to send to database
                                String delete = "Delete from flight where flight_num = "
                                                + flight.getText()
                                                + " and to_char(depart_ts, 'MMDDYYYY') = " + month
                                                + dayDate.getSelectedItem()
                                                + yearDate.getSelectedItem();
                                base.executeUpdate(delete);
                                base.sqlcommit();
                                frame.setLocation(delFrame.getLocation());
                                delFrame.dispose();
                                frame.setVisible(true);
                        }
                });
                JButton cancel = new JButton("cancel");
                cancel.addActionListener(new ActionListener() {
                        public void actionPerformed(ActionEvent e) {
                                aircraftFrame.setLocation(delFrame.getLocation());
                                delFrame.dispose();
                                aircraftFrame.setVisible(true);
                        }
                });

                JPanel flightPanel = new JPanel();
                flightPanel.add(flightText);
                flightPanel.add(flight);
                JPanel buttons = new JPanel();
                buttons.add(submit);
                buttons.add(cancel);

                delPanel.add(flightPanel);

                delPanel.add(date);

                delPanel.add(buttons);

                delFrame.getContentPane().add(delPanel);
        }

        public static boolean validateDate(String month, int day, int year) {
                // February
                if (month.equals("February")) {
                        if (day > 28) {
                                return false;
                        }
                }
                // 30 days have September, April, June and November
                else if (month.equals("September") || month.equals("April")
                                || month.equals("June") || month.equals("November")) {
                        if (day > 30) {
                                return false;
                        }
                }

                return true;
        }

        public static boolean validateSeat(String seatString) {
                if (seatString.length() != 3) {
                        return false;
                }

                try {
                        Integer.parseInt(seatString.substring(0, 2));
                } catch (Exception e) {
                        return false;
                }

                return (seatString.charAt(2) >= 65 && seatString.charAt(2) <= 122);

        }

        public static boolean validateTime(int time) {
                int hours = time / 100;
                int minutes = time % 100;

                if (hours < 0 || hours > 23) {
                        return false;
                }

                if (minutes < 0 || minutes > 59) {
                        return false;
                }
                return true;

        }

        private static class ComboBoxWrapper {
                private static ArrayList dropdowns;
                private static ArrayList removedCities;
                private static String[] cities = { "New York", "Washington D.C.",
                                "Baltimore", "L.A.", "San Francisco", "Seattle", "Chicago",
                                "Newark", "Detroit", "Boston", "Raleigh", "Tucson", "Columbus",
                                "Tampa", "Houston", "Las Vegas", "Minneapolis", "Atlanta",
                                "Pittsburgh", "Toronto" };

                public ComboBoxWrapper() {
                        dropdowns = new ArrayList();
                        removedCities = new ArrayList();
                }

                public int addComboBox(JComboBox jcb) {
                        int size = dropdowns.size();

                        for (int i = 0; i < removedCities.size(); i++) {
                                jcb.removeItem(removedCities.get(i));
                                jcb.revalidate();
                                jcb.repaint();
                        }
                        dropdowns.add(jcb);
                        return size;
                }

                public static void remove(String city) {
                        removedCities = new ArrayList();
                        int dropdownIndex = -1;
                        int i = 0;
                        while (dropdownIndex < 0 && i < dropdowns.size()) {
                                String selItem = (String) ((JComboBox) dropdowns.get(i))
                                                .getSelectedItem();
                                if (selItem != null && selItem.equals(city)) {
                                        dropdownIndex = i;
                                        removedCities.add(selItem);
                                } else {
                                        if (selItem != null) {
                                                removedCities.add(selItem);
                                        }
                                }
                                i++;
                        }

                        if (dropdownIndex != -1) {
                                for (i = dropdownIndex + 1; i < dropdowns.size(); i++) {
                                        JComboBox temp = ((JComboBox) dropdowns.get(i));
                                        String selected = (String) temp.getSelectedItem();
                                        temp.removeAllItems();
                                        int index = -1;
                                        for (int j = 0; j < cities.length; j++) {
                                                if (removedCities.indexOf(cities[j]) == -1) {
                                                        temp.addItem(cities[j]);
                                                } else {
                                                        if (selected != null && selected.equals(cities[j])) {
                                                                selected = null;
                                                        }
                                                }
                                        }

                                        temp.setSelectedItem(selected);
                                        temp.revalidate();
                                        temp.repaint();
                                }
                        }
                }

                public static void removeComboBox() {
                        dropdowns.remove(dropdowns.size() - 1);
                }

                public JComboBox getComboBox(int index) {
                        return (JComboBox) dropdowns.get(index);
                }
        }

        private static class FlightSelectorWrapper {
                private JComboBox flights;
                private JPanel panel;
                private String[] details;

                public FlightSelectorWrapper() {
                        panel = new JPanel();

                }

                public JPanel getPanel() {
                        return panel;
                }

                public void populate(String[] list) {
                        details = new String[list.length];
                        String[] arr = new String[list.length];
                        for (int i = 0; i < list.length; i++) {
                                details[i] = list[i];
                                String[] item = list[i].split(",");
                                int numStops = Integer.parseInt(item[2])
                                                - Integer.parseInt(item[1]) - 1;
                                String stopStr;
                                if (numStops == 0) {
                                        stopStr = "(nonstop)";
                                } else {
                                        stopStr = "(" + numStops + " stop(s))";
                                }

                                String flightstr = "Flight " + item[0] + " departing at "
                                                + item[4] + " and arriving at " + item[3] + " "
                                                + stopStr;
                                arr[i] = flightstr;
                        }

                        panel.removeAll();
                        flights = new JComboBox(arr);
                        panel.add(flights);
                        panel.revalidate();
                        panel.repaint();
                }

                public String getSelectedFlight() {

                        if (flights == null) {
                                return null;
                        } else {
                                int index = flights.getSelectedIndex();
                                return details[index];
                        }
                }

                public void setEmpty() {
                        panel.removeAll();
                        flights = null;
                        details = null;
                        panel.add(new JLabel("No flights found."));
                        panel.revalidate();
                        panel.repaint();
                }
        }

        private static class PanelWrapper {
                private JScrollPane scroll;
                private JPanel panel;
                private int count;
                private ArrayList cities;

                public PanelWrapper() {
                        cities = new ArrayList();
                        panel = new JPanel(new GridLayout(0, 1));
                        scroll = new JScrollPane(panel);
                        scroll
                                        .setVerticalScrollBarPolicy(JScrollPane.VERTICAL_SCROLLBAR_AS_NEEDED);
                        scroll.setMaximumSize(new Dimension(500, 200));
                        scroll.setMinimumSize(new Dimension(500, 200));
                        count = 0;
                }

                public void add(JPanel p) {
                        panel.add(p);
                        scroll.revalidate();
                        count++;
                }

                public JScrollPane getPanel() {
                        return scroll;
                }

                public ArrayList getPanelLocations() {
                        int numLocs = panel.getComponentCount();
                        ArrayList data = new ArrayList();
                        for (int i = 0; i < numLocs; i++) {
                                JPanel innerPanel = (JPanel) panel.getComponent(i);
                                String temp = "";
                                if (((JComboBox) innerPanel.getComponent(4)).getSelectedItem() != null) {
                                        temp = ((JTextField) innerPanel.getComponent(3)).getText()
                                                        + ","
                                                        + ((JComboBox) innerPanel.getComponent(4))
                                                                        .getSelectedItem()
                                                        + ","
                                                        + ((JTextField) innerPanel.getComponent(5))
                                                                        .getText();

                                }
                                System.out.println(temp);
                                if (!temp.equals("")) {
                                        data.add(temp);
                                }

                        }

                        return data;
                }

                public void removeLeg() {
                        panel.remove(panel.getComponentCount() - 1);
                }

                public int getCount() {
                        return panel.getComponentCount();
                }

        }

}